
import com.microsoft.schemas.office.visio.x2012.main.CellType;
import entity.MapPointExcelEntity;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class PointExcelParseTool {
    private String mFilePath;
    private List<MapPointExcelEntity> mFinalData = new ArrayList<MapPointExcelEntity>();

    void setFilePath(String filePath) {
        mFilePath = filePath;
    }

    private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    Workbook initWorkBook() throws IOException {
        File file = new File(mFilePath);
        InputStream is = new FileInputStream(file);

        Workbook workbook = null;
        if (mFilePath.endsWith(SUFFIX_2003)) {
            workbook = new HSSFWorkbook(is);
        } else if (mFilePath.endsWith(SUFFIX_2007)) {
            workbook = new XSSFWorkbook(is);
        }

        return workbook;
    }

    void parseWorkbook(Workbook workbook, List<MapPointExcelEntity> list) {//判断有几张表
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; ++i) {
            Sheet sheetAt = workbook.getSheetAt(i);
            parseSheet(sheetAt, list);
        }
    }

    /**
     * 解析单张表
     *
     * @param sheet
     * @param list
     */
    void parseSheet(Sheet sheet, List<MapPointExcelEntity> list) {
        Row row;
        int count = 0;
        Iterator<Row> iterator = sheet.iterator();
        while (iterator.hasNext()) {
            row = iterator.next();
            if (count == 0) {//第一行为每列的名字

            } else {
                parseRowAndSetData(row, list);
            }

            ++count;
        }
    }

    private void parseRowAndSetData(Row row, List<MapPointExcelEntity> list) {
        List<String> rowData = parseRow(row);
        MapPointExcelEntity entity = new MapPointExcelEntity();
        for (int i = 0; i < rowData.size(); i++) {

            switch (i) {
                case 0:
                    entity.setRoadName(rowData.get(i));
                    break;
                case 1:
                    entity.setVoltageLevel(rowData.get(i));
                    break;
                case 2:
                    entity.setManageName(rowData.get(i));
                    break;
                case 3:
                    entity.setTowerNumber(rowData.get(i));
                    break;
                case 4:
                    entity.setLongitude(rowData.get(i));
                    break;
                case 5:
                    entity.setLatitude(rowData.get(i));
                    break;
            }

        }
        list.add(entity);
    }


    /**
     * 将每行每格数据存到集合
     *
     * @param row
     * @return
     */
    private List<String> parseRow(Row row) {
        List<String> rst = new ArrayList<String>();
        Cell cell;
        Iterator<Cell> iterator = row.iterator();
        while (iterator.hasNext()) {
            cell = iterator.next();//每格
            int cellType = cell.getCellType();
            switch (cellType) {
                case Cell.CELL_TYPE_NUMERIC:
                    double numericCellValue = cell.getNumericCellValue();
                    rst.add(String.valueOf(numericCellValue));
                    break;
                case Cell.CELL_TYPE_STRING:
                    String stringCellValue = cell.getStringCellValue();
                    rst.add(stringCellValue);
                    break;
                default:
                    break;

            }
        }
        return rst;
    }
}
